﻿
Imports System.Data
Imports System.Data.SqlClient


Namespace Seguridad
    Public Class Usuario

        Public Function Listar(ByVal UsuarioId As Integer) As Entity.Seguridad.Usuario

            Dim strNameProcedure As String = "up_Sec_Usuario_obtener"
            Dim sqlReader As SqlDataReader = Nothing
            Dim sqlCon As SqlConnection = New SqlConnection(Configuracion.Connexion.Cnn)
            Dim objEnt As entity.Seguridad.Usuario
            Try
                sqlCon.Open()
                Dim sqlCmd As SqlCommand = New SqlCommand(strNameProcedure, sqlCon)
                With sqlCmd
                    .CommandType = CommandType.StoredProcedure
                    .Parameters.Add(New SqlParameter("@UsuarioId", SqlDbType.Int)).Value = UsuarioId
                End With
                sqlReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection)
                If sqlReader.HasRows Then
                    While sqlReader.Read
                        objEnt = New entity.Seguridad.Usuario
                        With objEnt
                            .UsuarioId = sqlReader("UsuarioId").ToString
                            .PersonaID = sqlReader("PersonaID").ToString
                            .Login = sqlReader("Login").ToString
                            .Password = sqlReader("Password").ToString
                            .Estado = sqlReader("Estado").ToString
                            .RolId = sqlReader("RolId").ToString
                            ._Usu_Cre = sqlReader("_Usu_Cre").ToString
                            ._Fec_Cre = sqlReader("_Fec_Cre").ToString
                            ._Usu_Mod = sqlReader("_Usu_Mod").ToString
                            ._Fec_Mod = sqlReader("_Fec_Mod").ToString
                            ._flg_eli = sqlReader("_flg_eli").ToString
                        End With
                    End While
                End If
            Catch ex As Exception
                Throw New Exception(ex.ToString)
            Finally
                sqlReader.Dispose()
                sqlReader.Close()
                sqlReader = Nothing
                sqlCon.Dispose()
                sqlCon.Close()
                sqlCon = Nothing
            End Try
            Return objEnt

        End Function


        '***************************



        Public Function Listar(ByVal Opcion As String, _
          ByVal UsuarioId As Integer, _
          ByVal PersonaID As Integer, _
          ByVal Login As String, _
          ByVal Password As String, _
          ByVal Estado As Integer, _
          ByVal RolId As Integer) _
        As Entity.Seguridad.UsuarioCol

            Dim strNameProcedure As String = "up_Sec_Usuario_listar"
            Dim objCol As Entity.Seguridad.UsuarioCol
            Try
                Using conn As New SqlConnection(Configuracion.Connexion.Cnn)
                    Dim cmd As New SqlCommand(strNameProcedure, conn)
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Parameters.Add(New SqlParameter("@opcion", SqlDbType.VarChar, 50)).Value = Opcion
                    cmd.Parameters.Add(New SqlParameter("@UsuarioId", SqlDbType.Int)).Value = UsuarioId
                    cmd.Parameters.Add(New SqlParameter("@PersonaID", SqlDbType.Int)).Value = PersonaID
                    cmd.Parameters.Add(New SqlParameter("@Login", SqlDbType.VarChar, 50)).Value = Login
                    cmd.Parameters.Add(New SqlParameter("@Password", SqlDbType.VarChar, 50)).Value = Password
                    cmd.Parameters.Add(New SqlParameter("@Estado", SqlDbType.Int)).Value = Estado
                    cmd.Parameters.Add(New SqlParameter("@RolId", SqlDbType.Int)).Value = RolId

                    conn.Open()
                    Using dr As SqlDataReader = cmd.ExecuteReader()
                        Dim iUsuarioId As Integer = dr.GetOrdinal("UsuarioId")
                        Dim iPersonaID As Integer = dr.GetOrdinal("PersonaID")
                        Dim iLogin As Integer = dr.GetOrdinal("Login")
                        Dim iPassword As Integer = dr.GetOrdinal("Password")
                        Dim iEstado As Integer = dr.GetOrdinal("Estado")
                        Dim iRolId As Integer = dr.GetOrdinal("RolId")
                        Dim i_Usu_Cre As Integer = dr.GetOrdinal("_Usu_Cre")
                        Dim i_Fec_Cre As Integer = dr.GetOrdinal("_Fec_Cre")
                        Dim i_Usu_Mod As Integer = dr.GetOrdinal("_Usu_Mod")
                        Dim i_Fec_Mod As Integer = dr.GetOrdinal("_Fec_Mod")
                        Dim i_flg_eli As Integer = dr.GetOrdinal("_flg_eli")

                        Dim colCount As Integer = dr.FieldCount
                        Dim values As Object() = New Object(colCount - 1) {}

                        objCol = New Entity.Seguridad.UsuarioCol
                        If dr.HasRows Then
                            While dr.Read()
                                Dim objEnt = New Entity.Seguridad.Usuario
                                dr.GetValues(values)

                                objEnt.UsuarioId = values(iUsuarioId)
                                objEnt.PersonaID = values(iPersonaID)
                                objEnt.Login = values(iLogin)
                                objEnt.Password = values(iPassword)
                                objEnt.Estado = values(iEstado)
                                objEnt.RolId = values(iRolId)
                                objEnt._Usu_Cre = values(i_Usu_Cre)
                                objEnt._Fec_Cre = values(i_Fec_Cre)
                                objEnt._Usu_Mod = values(i_Usu_Mod)
                                objEnt._Fec_Mod = values(i_Fec_Mod)
                                objEnt._flg_eli = values(i_flg_eli)
                                objCol.Add(objEnt)
                            End While
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Throw New Exception(ex.ToString)
            Finally
            End Try
            Return objCol

        End Function


        '***************************


        Public Shared Function Insert(ByVal opcion As String, _
          ByVal UsuarioId As Integer, _
          ByVal PersonaID As Integer, _
          ByVal Login As String, _
          ByVal Password As String, _
          ByVal Estado As Boolean, _
          ByVal RolId As Integer, _
          ByVal _Usu_Cre As Integer, _
          ByVal _Fec_Cre As Integer, _
          ByVal _Usu_Mod As Integer, _
          ByVal _Fec_Mod As Integer, _
          ByVal _flg_eli As Boolean) _
         As Integer

            Dim strNameProcedure As String = "Up_Sec_Usuario_Insertar"
            Try
                Dim intCodigo As Integer = -1
                Using sqlCon As SqlConnection = New SqlConnection(Configuracion.Connexion.Cnn)
                    Using sqlCmd As SqlCommand = New SqlCommand(strNameProcedure, sqlCon)
                        With sqlCmd
                            .CommandType = CommandType.StoredProcedure
                            .Parameters.Add(New SqlParameter("@opcion", SqlDbType.VarChar, 120)).Value = opcion
                            .Parameters.Add(New SqlParameter("@UsuarioId", SqlDbType.INT)).Value = UsuarioId
                            .Parameters.Add(New SqlParameter("@PersonaID", SqlDbType.INT)).Value = PersonaID
                            .Parameters.Add(New SqlParameter("@Login", SqlDbType.VARCHAR, 50)).Value = Login
                            .Parameters.Add(New SqlParameter("@Password", SqlDbType.VARCHAR, 50)).Value = Password
                            .Parameters.Add(New SqlParameter("@Estado", SqlDbType.BIT)).Value = Estado
                            .Parameters.Add(New SqlParameter("@RolId", SqlDbType.INT)).Value = RolId
                            .Parameters.Add(New SqlParameter("@_Usu_Cre", SqlDbType.INT)).Value = _Usu_Cre
                            .Parameters.Add(New SqlParameter("@_Fec_Cre", SqlDbType.BIGINT)).Value = _Fec_Cre
                            .Parameters.Add(New SqlParameter("@_Usu_Mod", SqlDbType.INT)).Value = _Usu_Mod
                            .Parameters.Add(New SqlParameter("@_Fec_Mod", SqlDbType.BIGINT)).Value = _Fec_Mod
                            .Parameters.Add(New SqlParameter("@_flg_eli", SqlDbType.BIT)).Value = _flg_eli
                            .Parameters.Add(New SqlParameter("@variable_out", SqlDbType.Int))
                            .Parameters("@variable_out").Direction = ParameterDirection.Output
                            sqlCon.Open()
                            .ExecuteNonQuery()
                            intCodigo = .Parameters("@variable_out").Value
                            Return intCodigo
                        End With
                    End Using
                End Using
            Catch ex As Exception
                Throw New Exception(ex.Message)
                Return -1
            End Try

        End Function


        '***************************


        Public Shared Function Update(ByVal opcion As String, _
          ByVal UsuarioId As Integer, _
          ByVal PersonaID As Integer, _
          ByVal Login As String, _
          ByVal Password As String, _
          ByVal Estado As Boolean, _
          ByVal RolId As Integer, _
          ByVal _Usu_Cre As Integer, _
          ByVal _Fec_Cre As Integer, _
          ByVal _Usu_Mod As Integer, _
          ByVal _Fec_Mod As Integer, _
          ByVal _flg_eli As Boolean) _
         As Integer

            Dim strNameProcedure As String = "Up_Sec_Usuario_Actualizar"
            Try
                Dim intCodigo As Integer = -1
                Using sqlCon As SqlConnection = New SqlConnection(Configuracion.Connexion.Cnn)
                    Using sqlCmd As SqlCommand = New SqlCommand(strNameProcedure, sqlCon)
                        With sqlCmd
                            .CommandType = CommandType.StoredProcedure
                            .Parameters.Add(New SqlParameter("@opcion", SqlDbType.VarChar, 120)).Value = opcion
                            .Parameters.Add(New SqlParameter("@UsuarioId", SqlDbType.INT)).Value = UsuarioId
                            .Parameters.Add(New SqlParameter("@PersonaID", SqlDbType.INT)).Value = PersonaID
                            .Parameters.Add(New SqlParameter("@Login", SqlDbType.VARCHAR, 50)).Value = Login
                            .Parameters.Add(New SqlParameter("@Password", SqlDbType.VARCHAR, 50)).Value = Password
                            .Parameters.Add(New SqlParameter("@Estado", SqlDbType.BIT)).Value = Estado
                            .Parameters.Add(New SqlParameter("@RolId", SqlDbType.INT)).Value = RolId
                            .Parameters.Add(New SqlParameter("@_Usu_Cre", SqlDbType.INT)).Value = _Usu_Cre
                            .Parameters.Add(New SqlParameter("@_Fec_Cre", SqlDbType.BIGINT)).Value = _Fec_Cre
                            .Parameters.Add(New SqlParameter("@_Usu_Mod", SqlDbType.INT)).Value = _Usu_Mod
                            .Parameters.Add(New SqlParameter("@_Fec_Mod", SqlDbType.BIGINT)).Value = _Fec_Mod
                            .Parameters.Add(New SqlParameter("@_flg_eli", SqlDbType.BIT)).Value = _flg_eli
                            .Parameters.Add(New SqlParameter("@variable_out", SqlDbType.Int))
                            .Parameters("@variable_out").Direction = ParameterDirection.Output
                            sqlCon.Open()
                            .ExecuteNonQuery()
                            intCodigo = .Parameters("@variable_out").Value
                            Return intCodigo
                        End With
                    End Using
                End Using
            Catch ex As Exception
                Throw New Exception(ex.Message)
                Return -1
            End Try

        End Function


        '***************************


        Public Shared Function Eliminar(ByVal opcion As String, _
          ByVal UsuarioId As Integer, _
          ByVal PersonaID As Integer, _
          ByVal Login As String, _
          ByVal Password As String, _
          ByVal Estado As Boolean, _
          ByVal RolId As Integer, _
          ByVal _Usu_Cre As Integer, _
          ByVal _Fec_Cre As Integer, _
          ByVal _Usu_Mod As Integer, _
          ByVal _Fec_Mod As Integer, _
          ByVal _flg_eli As Boolean) _
         As Integer

            Dim strNameProcedure As String = "Up_Sec_Usuario_Eliminar"
            Try
                Dim intCodigo As Integer = -1
                Using sqlCon As SqlConnection = New SqlConnection(Configuracion.Connexion.Cnn)
                    Using sqlCmd As SqlCommand = New SqlCommand(strNameProcedure, sqlCon)
                        With sqlCmd
                            .CommandType = CommandType.StoredProcedure
                            .Parameters.Add(New SqlParameter("@opcion", SqlDbType.VarChar, 120)).Value = opcion
                            .Parameters.Add(New SqlParameter("@UsuarioId", SqlDbType.Int)).Value = UsuarioId
                            .Parameters.Add(New SqlParameter("@variable_out", SqlDbType.Int))
                            .Parameters("@variable_out").Direction = ParameterDirection.Output
                            sqlCon.Open()
                            .ExecuteNonQuery()
                            intCodigo = .Parameters("@variable_out").Value
                            Return intCodigo
                        End With
                    End Using
                End Using
            Catch ex As Exception
                Throw New Exception(ex.Message)
                Return -1
            End Try

        End Function


        '***************************



    End Class



End Namespace

